from fastapi import FastAPI, Depends, HTTPException, status
from mysql.connector import connect, Error
from pydantic import BaseModel, Field
from typing import List, Optional
from fastapi.middleware.cors import CORSMiddleware
from datetime import datetime, date
import json
from fastapi import Depends, HTTPException, status
from pydantic import BaseModel
from mysql.connector import Error
from datetime import datetime
from typing import Dict
from sqlalchemy.orm import Session
app = FastAPI(title="医疗信息管理系统API", description="提供学生、账号和检测数据的管理接口")

DATABASE_CONFIG = {
    'host': '10.11.48.101',
    'port': 3307,
    'database': 'qyxmsx_0006_24252_stu_202210139187',
    'user': 'stu_202210139187',
    'password': 'Temp@202210139187'
}

# 数据库连接依赖
def get_db():
    try:
        connection = connect(**DATABASE_CONFIG)
        yield connection
    except Error as e:
        print(f"Error connecting to database: {e}")
        raise HTTPException(status_code=500, detail=f"Database connection error: {str(e)}")
    finally:
        if 'connection' in locals() and connection.is_connected():
            connection.close()

# Pydantic模型 - 个人信息表
class Student(BaseModel):
    student_id: str
    student_name: str
    gender: str
    major: str
    classname: str
    grade: str

# Pydantic模型 - 账号密码表
class AccountBase(BaseModel):
    student_id: str
    username: str

    class Config:
        from_attributes = True


class AccountCreate(AccountBase):
    password: str = Field(..., min_length=6, max_length=100)


class Account(AccountBase):
    create_time: str
    last_login_time: str
    status: int

# Pydantic模型 - 检测数据表
class Detection(BaseModel):
    row_id: int
    id: str
    createTime: str
    updateTime: str
    userId: str
    companyId: str
    idNumberAes: str
    finalCorporeity: str
    countDetail: str
    name: str
    gender: int
    birthday: str
    age: int
    height: float
    weight: float
    phone: str
    contact: str
    faceImg: str
    faceIsCheck: str
    tongueImg: str
    remark: str
    isFormal: str
    phIsUp: str
    phIsDossier: str
    summaryV1: str
    created: str
    student_id: str
    constitutionPrediction: str  # 添加新字段

    class Config:
        from_attributes = True

# 注册请求模型
class RegisterRequest(BaseModel):
    student_id: str = Field(..., min_length=8, max_length=20)
    username: str = Field(..., min_length=2, max_length=50)  # 添加 username 字段
    student_name: str = Field(..., min_length=2, max_length=50)
    gender: str = Field(..., min_length=1, max_length=10)
    major: str = Field(..., min_length=2, max_length=50)
    classname: str = Field(..., min_length=2, max_length=50)
    grade: str = Field(..., min_length=2, max_length=20)
    password: str = Field(..., min_length=6, max_length=100)

# 登录请求模型
class LoginRequest(BaseModel):
    student_id: str
    password: str

# 登录响应模型
class LoginResponse(BaseModel):
    student_id: str
    username: str
    message: str

# 管理员登录请求模型
class AdminLoginRequest(BaseModel):
    admin_id: str
    password: str

# 管理员注册请求模型
class AdminRegisterRequest(BaseModel):
    admin_id: str
    username: str
    password: str
    role: str = "admin"

# 根路径
@app.get("/")
def root():
    return {
        "message": "API服务运行中",
        "docs": "请访问 http://127.0.0.1:8000/docs 查看API文档",
        "available_routes": [
            "/students/",
            "/students/{student_id}",
            "/accounts/",
            "/accounts/{student_id}",
            "/detections/",
            "/detections/{student_id}",
            "/register/",
            "/login/"
        ]
    }

# 获取单个学生
@app.get("/students/{student_id}", response_model=Student)
def read_student(student_id: str, db=Depends(get_db)):
    cursor = db.cursor(dictionary=True)
    try:
        query = "SELECT * FROM 个人信息表 WHERE student_id = %s"
        cursor.execute(query, (student_id,))
        student = cursor.fetchone()
        if not student:
            raise HTTPException(status_code=404, detail="学生不存在")
        return student
    except Error as e:
        raise HTTPException(status_code=500, detail=f"获取学生信息失败: {str(e)}")
    finally:
        cursor.close()

# 获取单个账号
@app.get("/accounts/{student_id}", response_model=Account)
def read_account(student_id: str, db=Depends(get_db)):
    cursor = db.cursor(dictionary=True)
    try:
        query = "SELECT * FROM 账号密码表 WHERE student_id = %s"
        cursor.execute(query, (student_id,))
        account = cursor.fetchone()
        if not account:
            raise HTTPException(status_code=404, detail="账号不存在")
        # 将 create_time 和 last_login_time 转换为字符串
        account['create_time'] = str(account['create_time'])
        account['last_login_time'] = str(account['last_login_time'])
        return account
    except Error as e:
        raise HTTPException(status_code=500, detail=f"获取账号信息失败: {str(e)}")
    finally:
        cursor.close()

# 获取单个学生的检测数据
@app.get("/detections/{student_id}", response_model=List[Detection])
def read_detections_by_student(student_id: str, db=Depends(get_db)):
    cursor = db.cursor(dictionary=True)
    try:
        query = "SELECT * FROM 检测数据表 WHERE student_id = %s"
        cursor.execute(query, (student_id,))
        detections = cursor.fetchall()
        processed_detections = []
        for detection in detections:
            for key, value in detection.items():
                if isinstance(value, datetime):
                    detection[key] = value.strftime("%Y-%m-%d %H:%M:%S")
                elif isinstance(value, date):
                    detection[key] = value.strftime("%Y-%m-%d")
                elif value is None:
                    detection[key] = ""

            # 格式化体质预测概率
            try:
                # 解析JSON数据
                probabilities = json.loads(detection['countDetail'])
                # 格式化概率数据
                formatted_probabilities = "体质预测概率:\n"
                for item in probabilities:
                    formatted_probabilities += f"- {item['cor']}: {item['prob']:.2f}%\n"
                # 将格式化后的字符串赋值给新的字段
                detection['constitutionPrediction'] = formatted_probabilities
            except (KeyError, json.JSONDecodeError):
                detection['constitutionPrediction'] = "体质预测概率: 数据解析失败"

            # 格式化检查结论
            try:
                # 解析summaryV1字段
                summary = json.loads(detection['summaryV1'])

                # 检查数据结构是否符合预期
                if not isinstance(summary, dict) or 'moduleList' not in summary:
                    raise ValueError("Invalid summaryV1 format")

                formatted_summary = ""
                # 定义需要提取的模块及其对应显示名称（包含冒号的版本）
                modules_to_extract = {
                    "体质解析：": "体质解析",
                    "体质表现：": "体质表现",
                    "患病倾向：": "患病倾向",
                    "饮食疗养：": "饮食疗养",
                    "穴位保健：": "穴位保健",
                }

                # 提取并格式化每个模块的内容
                for module in summary.get('moduleList', []):
                    module_name = module.get('moduleName', '')
                    # 检查模块名称是否在需要提取的列表中
                    if module_name in modules_to_extract:
                        display_name = modules_to_extract[module_name]
                        formatted_summary += f"{display_name}:\n"

                        # 获取描述列表
                        desc_list = module.get('descList', [])

                        for desc in desc_list:
                            # 检查desc是否为字典且包含descText字段
                            if isinstance(desc, dict) and 'descText' in desc:
                                # 获取descName作为前缀（如果有）
                                desc_name = desc.get('descName', '').strip()
                                if desc_name:
                                    formatted_summary += f"- {desc_name} {desc['descText']}\n"
                                else:
                                    formatted_summary += f"- {desc['descText']}\n"

                        formatted_summary += "\n"  # 添加空行分隔不同模块

                # 如果格式化后的内容为空，使用原始数据
                if not formatted_summary.strip():
                    formatted_summary = f"检查结论:\n{detection['summaryV1']}"

                detection['summaryV1'] = formatted_summary
            except (KeyError, json.JSONDecodeError, ValueError) as e:
                # 如果解析失败，保留原始数据并添加错误信息
                detection['summaryV1'] = f"检查结论解析失败: {str(e)}\n原始数据: {detection['summaryV1']}"

            processed_detections.append(detection)
        return processed_detections
    except Error as e:
        raise HTTPException(status_code=500, detail=f"获取学生检测数据失败: {str(e)}")
    finally:
        cursor.close()
# 注册
@app.post("/register/", response_model=dict, status_code=status.HTTP_201_CREATED)
def register_user(register_data: RegisterRequest, db=Depends(get_db)):
    cursor = db.cursor(dictionary=True)
    try:
        # 检查账号密码表中是否已存在该学号
        cursor.execute("SELECT * FROM 账号密码表 WHERE student_id = %s", (register_data.student_id,))
        account = cursor.fetchone()
        if account:
            raise HTTPException(status_code=400, detail="该学号已被注册")

        # 准备当前时间
        current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

        # 创建账号数据（明文存储密码）
        account_data = {
            'student_id': register_data.student_id,
            'username': register_data.username,
            'password': register_data.password,  # 明文存储
            'create_time': current_time,
            'last_login_time': current_time,
            'status': 1
        }

        # 插入账号数据
        account_query = """
        INSERT INTO 账号密码表 
        (student_id, username, password, create_time, last_login_time, status)
        VALUES (%(student_id)s, %(username)s, %(password)s, %(create_time)s, %(last_login_time)s, %(status)s)
        """
        cursor.execute(account_query, account_data)

        # 检查个人信息表中是否已存在该学号
        cursor.execute("SELECT * FROM 个人信息表 WHERE student_id = %s", (register_data.student_id,))
        student = cursor.fetchone()

        if student:
            # 如果该学号已存在，则更新个人信息
            student_query = """
            UPDATE 个人信息表 
            SET student_name = %s, gender = %s, major = %s, classname = %s, grade = %s 
            WHERE student_id = %s
            """
            student_values = (
                register_data.student_name,
                register_data.gender,
                register_data.major,
                register_data.classname,
                register_data.grade,
                register_data.student_id
            )
            cursor.execute(student_query, student_values)
        else:
            # 如果该学号不存在，则插入个人信息
            student_data = {
                'student_id': register_data.student_id,
                'student_name': register_data.student_name,
                'gender': register_data.gender,
                'major': register_data.major,
                'classname': register_data.classname,
                'grade': register_data.grade
            }
            student_query = """
            INSERT INTO 个人信息表 
            (student_id, student_name, gender, major, classname, grade)
            VALUES (%(student_id)s, %(student_name)s, %(gender)s, %(major)s, %(classname)s, %(grade)s)
            """
            cursor.execute(student_query, student_data)

        # 提交事务
        db.commit()

        return {"message": "注册成功", "student_id": register_data.student_id}
    except Error as e:
        # 回滚事务
        db.rollback()
        raise HTTPException(status_code=500, detail=f"注册失败: {str(e)}")
    finally:
        cursor.close()
# 用户登录
@app.post("/login/", response_model=LoginResponse)
def login(login_data: LoginRequest, db=Depends(get_db)):
    cursor = db.cursor(dictionary=True)
    try:
        # 查询账号
        cursor.execute("SELECT * FROM 账号密码表 WHERE student_id = %s", (login_data.student_id,))
        account = cursor.fetchone()

        if not account:
            raise HTTPException(status_code=401, detail="账号不存在或密码错误")

        # 验证密码（直接对比明文）
        if account['password'] != login_data.password:
            raise HTTPException(status_code=401, detail="账号不存在或密码错误")

        # 更新最后登录时间
        current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        cursor.execute(
            "UPDATE 账号密码表 SET last_login_time = %s WHERE student_id = %s",
            (current_time, login_data.student_id)
        )
        db.commit()

        return {
            "student_id": account['student_id'],
            "username": account['username'],
            "message": "登录成功"
        }
    except Error as e:
        raise HTTPException(status_code=500, detail=f"登录失败: {str(e)}")
    finally:
        cursor.close()

# 更新最后登录时间
@app.post("/accounts/{student_id}/update_last_login")
def update_last_login(student_id: str, db=Depends(get_db)):
    cursor = db.cursor()
    try:
        current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        query = "UPDATE 账号密码表 SET last_login_time = %s WHERE student_id = %s"
        cursor.execute(query, (current_time, student_id))
        db.commit()
        return {"message": "最后登录时间更新成功"}
    except Error as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=f"更新最后登录时间失败: {str(e)}")
    finally:
        cursor.close()

# 读取用户信息API
@app.get("/accounts/{student_id}", response_model=Account)
def read_account(student_id: str, db=Depends(get_db)):
    cursor = db.cursor(dictionary=True)
    try:
        query = "SELECT * FROM 账号密码表 WHERE student_id = %s"
        cursor.execute(query, (student_id,))
        account = cursor.fetchone()
        if not account:
            raise HTTPException(status_code=404, detail="账号不存在")
        # 将 create_time 和 last_login_time 转换为字符串
        account['create_time'] = str(account['create_time'])
        account['last_login_time'] = str(account['last_login_time'])
        return account
    except Error as e:
        raise HTTPException(status_code=500, detail=f"获取账号信息失败: {str(e)}")
    finally:
        cursor.close()
# 获取所有学生信息
@app.get("/admin/students/", response_model=List[Student])
def read_all_students(db=Depends(get_db)):
    cursor = db.cursor(dictionary=True)
    try:
        query = "SELECT * FROM 个人信息表"
        cursor.execute(query)
        students = cursor.fetchall()
        return students
    except Error as e:
        raise HTTPException(status_code=500, detail=f"获取所有学生信息失败: {str(e)}")
    finally:
        cursor.close()
# 获取所有检测数据
@app.get("/detections/", response_model=List[Detection])
def read_all_detections(db=Depends(get_db)):
    cursor = db.cursor(dictionary=True)
    try:
        query = "SELECT * FROM 检测数据表"
        cursor.execute(query)
        detections = cursor.fetchall()
        processed_detections = []
        for detection in detections:
            for key, value in detection.items():
                if isinstance(value, datetime):
                    detection[key] = value.strftime("%Y-%m-%d %H:%M:%S")
                elif isinstance(value, date):
                    detection[key] = value.strftime("%Y-%m-%d")
                elif value is None:
                    detection[key] = ""

            # 格式化体质预测概率
            try:
                probabilities = json.loads(detection['countDetail'])
                formatted_probabilities = "体质预测概率:\n"
                for item in probabilities:
                    formatted_probabilities += f"- {item['cor']}: {item['prob']:.2f}%\n"
                detection['constitutionPrediction'] = formatted_probabilities
            except (KeyError, json.JSONDecodeError):
                detection['constitutionPrediction'] = "体质预测概率: 数据解析失败"

            # 格式化检查结论
            try:
                summary = json.loads(detection['summaryV1'])
                if not isinstance(summary, dict) or 'moduleList' not in summary:
                    raise ValueError("Invalid summaryV1 format")

                formatted_summary = ""
                modules_to_extract = {
                    "体质解析：": "体质解析",
                    "体质表现：": "体质表现",
                    "患病倾向：": "患病倾向",
                    "饮食疗养：": "饮食疗养",
                    "穴位保健：": "穴位保健",
                }
                for module in summary.get('moduleList', []):
                    module_name = module.get('moduleName', '')
                    if module_name in modules_to_extract:
                        display_name = modules_to_extract[module_name]
                        formatted_summary += f"{display_name}:\n"
                        desc_list = module.get('descList', [])
                        for desc in desc_list:
                            if isinstance(desc, dict) and 'descText' in desc:
                                desc_name = desc.get('descName', '').strip()
                                if desc_name:
                                    formatted_summary += f"- {desc_name} {desc['descText']}\n"
                                else:
                                    formatted_summary += f"- {desc['descText']}\n"
                        formatted_summary += "\n"

                # 如果格式化后的内容为空，使用原始数据
                if not formatted_summary.strip():
                    formatted_summary = f"检查结论:\n{detection['summaryV1']}"

                detection['summaryV1'] = formatted_summary

            except (KeyError, json.JSONDecodeError, ValueError) as e:
                detection['summaryV1'] = f"检查结论解析失败: {str(e)}\n原始数据: {detection['summaryV1']}"

            processed_detections.append(detection)
        return processed_detections
    except Error as e:
        raise HTTPException(status_code=500, detail=f"获取所有检测结果失败: {str(e)}")
    finally:
        cursor.close()
# 管理员登录
@app.post("/admins/login/", response_model=LoginResponse)
def admin_login(login_data: AdminLoginRequest, db=Depends(get_db)):
    cursor = db.cursor(dictionary=True)
    # 查询管理员表
    query = "SELECT * FROM 管理员表 WHERE admin_id = %s AND password = %s"
    cursor.execute(query, (login_data.admin_id, login_data.password))
    admin = cursor.fetchone()
    if not admin:
        raise HTTPException(status_code=401, detail="Invalid credentials")
    # 更新最后登录时间
    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    cursor.execute(
        "UPDATE 管理员表 SET last_login_time = %s WHERE admin_id = %s",
        (current_time, login_data.admin_id)
    )
    db.commit()
    return {
        "student_id": admin['admin_id'],
        "username": admin['username'],
        "message": "登录成功"
    }

# 用户统计
@app.get("/statistics/users/", response_model=dict)
def user_statistics(db=Depends(get_db)):
    cursor = db.cursor()
    try:
        # 统计用户总数
        cursor.execute("SELECT COUNT(*) FROM 个人信息表")
        total_users = cursor.fetchone()[0]

        return {"total_users": total_users}
    except Error as e:
        raise HTTPException(status_code=500, detail=f"用户统计失败: {str(e)}")
    finally:
        cursor.close()

# 检测数据统计
@app.get("/statistics/detections/", response_model=dict)
def detection_statistics(db=Depends(get_db)):
    cursor = db.cursor()
    try:
        # 统计检测数据总数
        cursor.execute("SELECT COUNT(*) FROM 检测数据表")
        total_detections = cursor.fetchone()[0]

        return {"total_detections": total_detections}
    except Error as e:
        raise HTTPException(status_code=500, detail=f"检测数据统计失败: {str(e)}")
    finally:
        cursor.close()

# 体质数量统计
@app.get("/statistics/constitutions/", response_model=dict)
def constitution_statistics(db=Depends(get_db)):
    cursor = db.cursor()
    try:
        # 统计不同体质的数量
        cursor.execute("SELECT finalCorporeity, COUNT(*) FROM 检测数据表 GROUP BY finalCorporeity")
        results = cursor.fetchall()
        constitution_count = {result[0]: result[1] for result in results}

        return {"constitution_count": constitution_count}
    except Error as e:
        raise HTTPException(status_code=500, detail=f"体质数量统计失败: {str(e)}")
    finally:
        cursor.close()

# 定义修改密码的请求模型
class ChangePasswordRequest(BaseModel):
    old_password: str
    new_password: str
# 修改密码
@app.post("/accounts/{student_id}/change_password")
def change_password(student_id: str, change_password_data: ChangePasswordRequest, db=Depends(get_db)):
    cursor = db.cursor()
    try:
        # 查询当前密码
        cursor.execute("SELECT password FROM 账号密码表 WHERE student_id = %s", (student_id,))
        account = cursor.fetchone()
        if not account:
            raise HTTPException(status_code=404, detail="账号不存在")

        # 验证旧密码
        if account[0] != change_password_data.old_password:
            raise HTTPException(status_code=401, detail="旧密码不正确")

        # 更新密码
        query = "UPDATE 账号密码表 SET password = %s WHERE student_id = %s"
        cursor.execute(query, (change_password_data.new_password, student_id))
        db.commit()
        return {"message": "密码修改成功"}
    except Error as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=f"修改密码失败: {str(e)}")
    finally:
        cursor.close()
# 定义更新学生信息的请求模型
class UpdateStudentInfoRequest(BaseModel):
    classname: str = None
    grade: str = None
    gender: str = None
    major: str = None
    student_name: str = None
# 更新学生信息
@app.put("/accounts/{student_id}/update_info")
def update_student_info(student_id: str, update_info_data: UpdateStudentInfoRequest, db=Depends(get_db)):
    try:
        cursor = db.cursor()
        set_clause = []
        values = []

        if update_info_data.classname is not None:
            set_clause.append("classname = %s")
            values.append(update_info_data.classname)
        if update_info_data.grade is not None:
            set_clause.append("grade = %s")
            values.append(update_info_data.grade)
        if update_info_data.gender is not None:
            set_clause.append("gender = %s")
            values.append(update_info_data.gender)
        if update_info_data.major is not None:
            set_clause.append("major = %s")
            values.append(update_info_data.major)
        if update_info_data.student_name is not None:
            set_clause.append("student_name = %s")
            values.append(update_info_data.student_name)

        if not set_clause:
            return {"message": "没有需要更新的字段"}

        set_clause_str = ", ".join(set_clause)
        update_query = f"UPDATE 个人信息表 SET {set_clause_str} WHERE student_id = %s"
        values.append(student_id)

        cursor.execute(update_query, values)
        db.commit()
        return {"message": "个人信息更新成功"}
    except Error as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=f"更新用户信息失败: {str(e)}")
    finally:
        if cursor:
            cursor.close()


class SurveyQuestionnaire(BaseModel):
    id: int | None = None  # 自增主键，默认为 None
    student_id: str
    student_name: str
    classname: str
    grade: str
    question_1: str
    question_2: str
    question_3: str
    question_4: str
    question_5: str
    question_6: str
    question_7: str
    question_8: str
    question_9: str
    question_10: str
    question_11: str
    question_12: str
    submit_time: datetime | None = None  # 问题提交时间，默认为 None

# 获取调查问卷问题列表的接口
@app.get("/survey/questions/", response_model=List[dict])
def get_survey_questions():
    questions = [
        {"id": 1, "text": "问题1：您是否感到焦虑？"},
        {"id": 2, "text": "问题2：您是否经常失眠？"},
        {"id": 3, "text": "问题3：您是否容易疲劳？"},
        {"id": 4, "text": "问题4：您是否有消化不良的情况？"},
        {"id": 5, "text": "问题5：您是否情绪低落？"},
        {"id": 6, "text": "问题6：您是否容易发怒？"},
        {"id": 7, "text": "问题7：您是否经常感冒？"},
        {"id": 8, "text": "问题8：您是否怕冷？"},
        {"id": 9, "text": "问题9：您是否手脚冰凉？"},
        {"id": 10, "text": "问题10：您是否喜欢热饮？"},
        {"id": 11, "text": "问题11：您是否口干舌燥？"},
        {"id": 12, "text": "问题12：您是否口苦？"}
    ]
    return questions


@app.post("/survey/submit/{student_id}", response_model=dict)
def submit_survey_answers(
        student_id: str,
        survey_data: Dict[str, str],
        db: Session = Depends(get_db)
):
    cursor = db.cursor(dictionary=True)
    try:
        # 根据学号查询个人信息
        query = "SELECT * FROM 个人信息表 WHERE student_id = %s"
        cursor.execute(query, (student_id,))
        student_info = cursor.fetchone()

        if not student_info:
            raise HTTPException(status_code=404, detail="学生信息不存在")

        # 提取需要的字段
        student_name = student_info['student_name']
        classname = student_info['classname']
        grade = student_info['grade']

        # 获取当前时间作为提交时间
        submit_time = datetime.now()

        # 插入调查问卷数据
        query = """
        INSERT INTO 调查问卷表 
        (student_id, student_name, classname, grade,
        question_1, question_2, question_3, question_4, question_5,
        question_6, question_7, question_8, question_9, question_10,
        question_11, question_12, submit_time)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        cursor.execute(query, (
            student_id,
            student_name,
            classname,
            grade,
            survey_data.get('question_1'),
            survey_data.get('question_2'),
            survey_data.get('question_3'),
            survey_data.get('question_4'),
            survey_data.get('question_5'),
            survey_data.get('question_6'),
            survey_data.get('question_7'),
            survey_data.get('question_8'),
            survey_data.get('question_9'),
            survey_data.get('question_10'),
            survey_data.get('question_11'),
            survey_data.get('question_12'),
            submit_time
        ))
        db.commit()
        return {"message": "问卷提交成功", "student_id": student_id}
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=f"问卷提交失败: {str(e)}")
    finally:
        cursor.close()# 问卷统计接口
@app.get("/statistics/surveys/", response_model=dict)
def survey_statistics(db=Depends(get_db)):
    cursor = db.cursor()
    try:
        # 统计问卷填写记录总数
        cursor.execute("SELECT COUNT(*) FROM 调查问卷表")
        total_surveys = cursor.fetchone()[0]

        return {"total_surveys": total_surveys}
    except Error as e:
        raise HTTPException(status_code=500, detail=f"问卷统计失败: {str(e)}")
    finally:
        cursor.close()
# 获取问卷填写记录接口
@app.get("/surveys/records/", response_model=List[dict])
def get_survey_records(db=Depends(get_db)):
    cursor = db.cursor(dictionary=True)
    try:
        # 查询所有问卷填写记录
        query = "SELECT * FROM 调查问卷表"
        cursor.execute(query)
        survey_records = cursor.fetchall()
        return survey_records
    except Error as e:
        raise HTTPException(status_code=500, detail=f"获取问卷记录失败: {str(e)}")
    finally:
        cursor.close()
@app.get("/survey/details/{student_id}", response_model=dict)
def get_survey_details(student_id: str, db=Depends(get_db)):
    cursor = db.cursor(dictionary=True, buffered=True)
    try:
        # 查询问卷填写记录
        query = "SELECT * FROM 调查问卷表 WHERE student_id = %s"
        cursor.execute(query, (student_id,))
        survey_record = cursor.fetchone()

        if not survey_record:
            raise HTTPException(status_code=404, detail="问卷记录不存在")

        # 获取问卷问题
        questions = get_survey_questions()

        # 构建问卷详情，包含问题和答案
        survey_details = {
            "id": survey_record["id"],  # 添加问卷 ID
            "student_id": survey_record["student_id"],
            "student_name": survey_record["student_name"],
            "classname": survey_record["classname"],
            "grade": survey_record["grade"],
            "submit_time": survey_record["submit_time"],  # 添加提交时间
            "answers": []
        }

        for question in questions:
            question_id = f"question_{question['id']}"
            answer = survey_record.get(question_id)
            survey_details["answers"].append({
                "question": question["text"],
                "answer": answer
            })

        return survey_details
    except Error as e:
        raise HTTPException(status_code=500, detail=f"获取问卷详情失败: {str(e)}")
    finally:
        # 确保仅关闭游标
        cursor.close()
@app.get("/statistics/gender_ratio/", response_model=dict)
def gender_ratio_statistics(db=Depends(get_db)):
    cursor = db.cursor()
    try:
        # 统计男性和女性的数量
        cursor.execute("SELECT gender, COUNT(*) FROM 个人信息表 GROUP BY gender")
        results = cursor.fetchall()
        gender_count = {result[0]: result[1] for result in results}

        return {"gender_count": gender_count}
    except Error as e:
        raise HTTPException(status_code=500, detail=f"男女比例统计失败: {str(e)}")
    finally:
        cursor.close()
# CORS配置
app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)